import duckdb
import uuid,io, pandas as pd
from service.nw_szy import nw_szy_service
import os
from utils.path_utils import get_project_root
from database.duckdb.duckdb_base import get_connection



# 创建 NW_SZY_MX 表
def create_table():
    con = get_connection()
    con.execute("""
        CREATE TABLE IF NOT EXISTS NW_SZY_MX (
          uuid VARCHAR(32) PRIMARY KEY,
          dwbh VARCHAR(100),
          model VARCHAR(200),
          xhbh VARCHAR(100),
          xhmc VARCHAR(1000),
          fybh VARCHAR(100),
          fymc VARCHAR(100),
          kjnd VARCHAR(10),
          kjqj VARCHAR(10),
          pzrq VARCHAR(100),
          pzbh VARCHAR(100),
          zdr VARCHAR(100),
          zy VARCHAR(1000),
          kmbh VARCHAR(100),
          kmmc VARCHAR(1000),
          jzfx VARCHAR(10),
          je DECIMAL,
          bmbh VARCHAR(100),
          bmmc VARCHAR(100),
          wldwbh VARCHAR(100),
          wldwmc VARCHAR(1000),
          zx01bh VARCHAR(100),
          zx01mc VARCHAR(1000),
          zx01qc VARCHAR(1000),
          zx02bh VARCHAR(100),
          zx02mc VARCHAR(1000),
          zx02qc VARCHAR(1000),
          zx99bh VARCHAR(100),
          zx99mc VARCHAR(1000),
          zx99qc VARCHAR(1000)
        )
    """)
    con.close()

# 获取所有记录
def get_all_data():
    con = get_connection()
    query = "SELECT * FROM NW_SZY_MX"
    result = con.execute(query).fetchall()
    con.close()
    return result

# 按条件获取记录
def get_data_by_condition(dwbh, model):
    con = get_connection()
    result = con.execute("SELECT * FROM NW_SZY_MX "
                         " WHERE dwbh = ? and model = ? ",
                         (dwbh, model)).fetchall()
    con.close()
    return result


# 根据 ID 获取单条记录
def get_data_by_uuid(uuid):
    con = get_connection()
    query = "SELECT * FROM NW_SZY_MX WHERE uuid = ?"
    result = con.execute(query, (uuid,)).fetchone()
    con.close()
    return result

# 添加记录
def add_data(uuid, dwbh, model, xhbh, xhmc, fybh, fymc, kjnd, kjqj, pzrq, pzbh, zdr, zy, kmbh, kmmc, jzfx, je, bmbh, bmmc, wldwbh, wldwmc, zx01bh, zx01mc, zx01qc, zx02bh, zx02mc, zx02qc, zx99bh, zx99mc, zx99qc):
    con = get_connection()
    con.execute("""
        INSERT INTO NW_SZY_MX (uuid, dwbh, model, xhbh, xhmc, fybh, fymc, kjnd, kjqj, pzrq, pzbh, zdr, zy, kmbh, kmmc, jzfx, je, bmbh, bmmc, wldwbh, wldwmc, zx01bh, zx01mc, zx01qc, zx02bh, zx02mc, zx02qc, zx99bh, zx99mc, zx99qc)
        VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    """, (uuid, dwbh, model, xhbh, xhmc, fybh, fymc, kjnd, kjqj, pzrq, pzbh, zdr, zy, kmbh, kmmc, jzfx, je, bmbh, bmmc, wldwbh, wldwmc, zx01bh, zx01mc, zx01qc, zx02bh, zx02mc, zx02qc, zx99bh, zx99mc, zx99qc))
    con.close()

# 更新记录
def update_data(uuid, dwbh, model, xhbh, xhmc, fybh, fymc, kjnd, kjqj, pzrq, pzbh, zdr, zy, kmbh, kmmc, jzfx, je, bmbh, bmmc, wldwbh, wldwmc, zx01bh, zx01mc, zx01qc, zx02bh, zx02mc, zx02qc, zx99bh, zx99mc, zx99qc):
    con = get_connection()
    con.execute("""
        UPDATE NW_SZY_MX
        SET dwbh = ?, mode = ?, xhbh = ?,xhmc = ?,fybh = ?,fymc = ?,kjnd = ?,kjqj = ?,pzrq = ?,pzbh = ?,zdr = ?,zy = ?,kmbh = ?,kmmc = ?,jzfx = ?,je = ?,bmbh = ?,bmmc = ?,wldwbh = ?,wldwmc = ?,zx01bh = ?,zx01mc = ?,zx01qc = ?,zx02bh = ?,zx02mc = ?,zx02qc = ?,zx99bh = ?,zx99mc = ?,zx99qc = ?
        WHERE uuid = ?
    """, ( dwbh, model, xhbh, xhmc, fybh, fymc, kjnd, kjqj, pzrq, pzbh, zdr, zy, kmbh, kmmc, jzfx, je, bmbh, bmmc, wldwbh, wldwmc, zx01bh, zx01mc, zx01qc, zx02bh, zx02mc, zx02qc, zx99bh, zx99mc, zx99qc, uuid))
    con.close()

# 删除记录
def delete_data(uuid):
    con = get_connection()
    con.execute("DELETE FROM NW_SZY_MX WHERE uuid = ?", (uuid,))
    con.close()



# 删除记录
def delete_data_by_model(p_dwbh, p_model, p_qzq_type):
    con = get_connection()
    con.execute("DELETE FROM NW_SZY_MX WHERE dwbh = $p_dwbh and model = $p_model and ($p_qzq_type is null or qzq_type = $p_qzq_type)",
                {'p_dwbh':p_dwbh, 'p_model':p_model, 'p_qzq_type':p_qzq_type})
    con.close()

def get_kmbh_yefx(p_dwbh, p_ledger, p_kjnd, p_kmbh) -> float:
    """
    获取科目余额方向
    :param p_dwbh: 单位编号
    :param p_ledger: 账套ID
    :param p_kjnd: 会计年度
    :param p_kmbh: 会计科目编号，允许非墨迹
    :return: 科目字典表中的科目余额方向， 1-借方， 2-贷方
    """
    l_chartofacc = "f80ff706-4f66-45ba-bb0f-639f46712316" # 科目体系
    l_chartofaccbh = "01" # 科目体系编号
    """
    企业科目体系 01   f80ff706-4f66-45ba-bb0f-639f46712316
    事业科目体系 02   fe8cef7b-71ce-46ab-8165-ce1b92b082a5
    基建科目体系 03   a5220819-7b5d-47df-9e62-397ea11280df
    工会科目体系 04   17f7f864-692c-4967-ac60-77f6b6398242
    党费科目体系 05   01dd5bfb-63a9-48d2-a9f8-fbb86f6557b3
    财务公司科目体系 06 71b2d031-9408-4c91-941d-67d444737b8f
    民间非营利科目体系 07    50a84c00-761d-4c31-8401-d043f5728a5b
    """
    con = get_connection()
    # JFFS 或 DFFS
    sql = f"""
    select balancedir 
      from T_API_KM t
     where (t.chartofacc = $l_chartofacc or t.chartofaccbh = $l_chartofaccbh)
       and t.code = $p_kmbh
       and t.fiyear = = $p_kjnd
       and (t.ledger is null or t.ledger = $p_ledger)
        """
    yefx = con.execute(sql, {'l_chartofacc':l_chartofacc,'l_chartofaccbh':l_chartofaccbh, 'p_ledger':p_ledger,'p_kjnd':p_kjnd,'p_kmbh':p_kmbh}).fetchone()[0]
    con.close()
    return yefx

def get_zwfzys_je(p_dwbh, p_ledger, p_kjnd, p_hsxmbh, p_kmbh, p_jzfx) -> float:
    """
    获取项目科目累计发生金额，借方或贷方
    :param p_dwbh: 单位编号
    :param p_ledger: 账套ID
    :param p_kjnd: 会计年度
    :param p_hsxmbh: 核算项目编号，允许非末级
    :param p_kmbh: 会计科目编号，允许非末级
    :param p_jzfx: 记账方向，1-借方，2-贷方
    :return: 按凭证辅助原始计算的累计发生金额
    """
    con = get_connection()
    # JFFS 或 DFFS
    sql = f"""
    select COALESCE(sum(t.je),0) as je 
      from v_temp_all_gscloud t
     where t.dwbh = $p_dwbh
       and t.ledger = $p_ledger
       and (t.year = $p_kjnd or $p_kjnd is null )
       and t.zx02bh like $p_hsxmbh||'%'  -- 项目
       and t.kmbh like $p_kmbh||'%'  -- 科目
       and t.jzfx = $p_jzfx  -- 记账方向
        """
    # print(sql)
    # print({'p_dwbh':p_dwbh,'p_ledger':p_ledger,'p_kjnd':p_kjnd,'p_hsxmbh':p_hsxmbh,'p_kmbh':p_kmbh,'p_jzfx':p_jzfx})
    je = con.execute(sql, {'p_dwbh':p_dwbh,'p_ledger':p_ledger,'p_kjnd':p_kjnd,'p_hsxmbh':p_hsxmbh,'p_kmbh':p_kmbh,'p_jzfx':p_jzfx}).fetchone()[0]
    # print('load_zwfzys_je:', je)
    con.close()
    return je

def load_zwfzys_mx(p_dwbh, p_ledger, p_model, p_qzq_type, p_kjnd, p_xhbh, p_xhmc, p_fybh, p_fymc, p_hsxmbh, p_kmbh, p_jzfx, p_flag):
    """
    获取项目科目累计发生凭证明细，借方或贷方，保存到收支余明细表中
    注：确保本方法执行前，相关历史或重复记录已被删除
    :param p_dwbh: 单位编号
    :param p_ledger: 账套ID
    :param p_model: 模型
    :param p_qzq_type: 全周期类型
    :param p_kjnd: 会计年度
    :param p_xhbh: 型号编号
    :param p_xhmc: 型号名称
    :param p_fybh: 费用编号
    :param p_fymc: 费用名称
    :param p_hsxmbh: 核算项目编号，允许非末级
    :param p_kmbh: 会计科目编号，允许非末级
    :param p_jzfx: 记账方向，1-借方，2-贷方
    :param p_flag: 金额计算方式, 1/-1
    """
    con = get_connection()
    # JFFS 或 DFFS
    sql = f"""
    insert into NW_SZY_MX (uuid, dwbh, model, qzq_type, xhbh, xhmc, fybh, fymc, 
    kjnd, kjqj, pzrq, pzbh, zdr, zy, kmbh, kmmc, jzfx, 
    je, calc_je, 
    bmbh, bmmc, wldwbh, wldwmc, 
    zx01bh, zx01mc, zx01qc, zx02bh, zx02mc, zx02qc, zx99bh, data_type)
    select uuid(), $p_dwbh, $p_model, $p_qzq_type, $p_xhbh, $p_xhmc, $p_fybh, $p_fymc, 
    t.year, t.kjqj, t.pzrq, t.pzbh, '' as zdr, t.zy, t.kmbh, t.kmmc, t.jzfx_zh, 
    t.je as je, t.je*$p_flag as calc_je, 
    '' as bmbh, '' as bmmc, t.wldwbh, t.wldwmc, 
    '' as zx01bh, '' as zx01mc, '' as zx01qc, t.zx02bh, t.zx02mc, t.zx02qc, t.zx99bh, '凭证明细' as data_type
      from v_temp_all_gscloud t
     where t.dwbh = $p_dwbh
       and t.ledger = $p_ledger
       and (t.year = $p_kjnd or $p_kjnd is null )
       and t.zx02bh like $p_hsxmbh||'%'  -- 项目
       and t.kmbh like $p_kmbh||'%'  -- 科目
       and t.jzfx = $p_jzfx  -- 记账方向
        """
    # print(sql)
    # print({'p_dwbh':p_dwbh,'p_ledger':p_ledger,'p_model':p_model, 'p_qzq_type':p_qzq_type,
    #                        'p_xhbh':p_xhbh, 'p_xhmc':p_xhmc, 'p_fybh':p_fybh, 'p_fymc':p_fymc,
    #                        'p_kjnd':p_kjnd,'p_hsxmbh':p_hsxmbh,'p_kmbh':p_kmbh,'p_jzfx':p_jzfx, 'p_flag':p_flag})
    con.execute(sql, {'p_dwbh':p_dwbh,'p_ledger':p_ledger,'p_model':p_model, 'p_qzq_type':p_qzq_type,
                           'p_xhbh':p_xhbh, 'p_xhmc':p_xhmc, 'p_fybh':p_fybh, 'p_fymc':p_fymc,
                           'p_kjnd':p_kjnd,'p_hsxmbh':p_hsxmbh,'p_kmbh':p_kmbh,'p_jzfx':p_jzfx, 'p_flag':p_flag})
    con.close()


def get_xmnc_je(p_dwbh, p_ledger, p_kjnd, p_hsxmbh, p_kmbh) -> float:
    """
    获取项目科目年初余额
    :param p_dwbh: 单位编号
    :param p_ledger: 账套ID
    :param p_kjnd: 会计年度
    :param p_hsxmbh: 核算项目编号，允许非末级
    :param p_kmbh: 会计科目编号，允许非末级
    :return: 按项目余额表年初数计算的年初余额
    """
    con = get_connection()
    sql = f"""
    select COALESCE(sum(case when t.jzfx = 1 then t.je else -1*t.je end), 0) as je 
      from v_temp_all_gscloud_nc t
     where t.dwbh = $p_dwbh
       and t.ledger = $p_ledger
       and (t.year = $p_kjnd or $p_kjnd is null )
       and t.zx02bh like $p_hsxmbh||'%'  -- 项目
       and t.kmbh like $p_kmbh||'%'  -- 科目
        """
    je = con.execute(sql, {'p_dwbh':p_dwbh,'p_ledger':p_ledger,'p_kjnd':p_kjnd,'p_hsxmbh':p_hsxmbh,'p_kmbh':p_kmbh}).fetchone()[0]
    con.close()
    return je

def load_xmnc_mx(p_dwbh, p_ledger, p_model, p_qzq_type, p_kjnd, p_xhbh, p_xhmc, p_fybh, p_fymc, p_hsxmbh, p_kmbh, p_flag):
    """
    获取项目科目年初余额明细，保存到收支余明细表中
    注：确保本方法执行前，相关历史或重复记录已被删除
    :param p_dwbh: 单位编号
    :param p_ledger: 账套ID
    :param p_model: 模型
    :param p_qzq_type: 全周期类型
    :param p_kjnd: 会计年度
    :param p_xhbh: 型号编号
    :param p_xhmc: 型号名称
    :param p_fybh: 费用编号
    :param p_fymc: 费用名称
    :param p_hsxmbh: 核算项目编号，允许非末级
    :param p_kmbh: 会计科目编号，允许非末级
    :param p_flag: 金额计算方式, 1/-1
    """

    con = get_connection()
    # 项目余额表
    sql = f"""
    insert into NW_SZY_MX (uuid, dwbh, model, qzq_type, xhbh, xhmc, fybh, fymc, 
    kjnd, kjqj, pzrq, pzbh, zdr, zy, kmbh, kmmc, jzfx, 
    je, calc_je,
    bmbh, bmmc, wldwbh, wldwmc, 
    zx01bh, zx01mc, zx01qc, zx02bh, zx02mc, zx02qc, zx99bh, data_type)
    select uuid(), $p_dwbh, $p_model, $p_qzq_type, $p_xhbh, $p_xhmc, $p_fybh, $p_fymc, 
    t.year, t.kjqj, t.pzrq, t.pzbh, '' as zdr, t.zy, t.kmbh, t.kmmc, t.jzfx_zh, 
    (case when t.jzfx = 1 then t.je else -1*t.je end) as je, 
    (case when t.jzfx = 1 then t.je else -1*t.je end)*$p_flag as calc_je, 
    '' as bmbh, '' as bmmc, t.wldwbh, t.wldwmc, 
    '' as zx01bh, '' as zx01mc, '' as zx01qc, t.zx02bh, t.zx02mc, t.zx02qc, t.zx99bh, '项目余额表年初数' as data_type
      from v_temp_all_gscloud_nc t
     where t.dwbh = $p_dwbh
       and t.ledger = $p_ledger
       and (t.year = $p_kjnd or $p_kjnd is null )
       and t.zx02bh like $p_hsxmbh||'%'  -- 项目
       and t.kmbh like $p_kmbh||'%'  -- 科目
        """
    # print(sql)
    # print({'p_dwbh':p_dwbh,'p_ledger':p_ledger,'p_model':p_model, 'p_qzq_type':p_qzq_type,
    #                        'p_xhbh':p_xhbh, 'p_xhmc':p_xhmc, 'p_fybh':p_fybh, 'p_fymc':p_fymc,
    #                        'p_kjnd':p_kjnd,'p_hsxmbh':p_hsxmbh,'p_kmbh':p_kmbh, 'p_flag':p_flag})
    con.execute(sql, {'p_dwbh':p_dwbh,'p_ledger':p_ledger,'p_model':p_model, 'p_qzq_type':p_qzq_type,
                           'p_xhbh':p_xhbh, 'p_xhmc':p_xhmc, 'p_fybh':p_fybh, 'p_fymc':p_fymc,
                           'p_kjnd':p_kjnd,'p_hsxmbh':p_hsxmbh,'p_kmbh':p_kmbh, 'p_flag':p_flag})
    con.close()

def get_xmnm_je(p_dwbh, p_ledger, p_kjnd, p_hsxmbh, p_kmbh) -> float:
    """
    获取项目科目年末余额
    科目余额方向为借方时 = 年初 + 借方 - 贷方
    科目余额方向为贷方时 = 年初 + 贷方 - 借方
    :param p_dwbh: 单位编号
    :param p_ledger: 账套ID
    :param p_kjnd: 会计年度
    :param p_hsxmbh: 核算项目编号，允许非末级
    :param p_kmbh: 会计科目编号，允许非末级
    :return: 按项目余额表年初数、借方发生数、贷方发生数计算的年初余额
    """
    yefx = get_kmbh_yefx(p_dwbh, p_ledger, p_kjnd, p_kmbh)
    if yefx == 1:
        return (get_xmnc_je(p_dwbh, p_ledger, p_kjnd, p_hsxmbh, p_kmbh)
                + get_zwfzys_je(p_dwbh, p_ledger, p_kjnd, p_hsxmbh, p_kmbh, 1)
                - get_zwfzys_je(p_dwbh, p_ledger, p_kjnd, p_hsxmbh, p_kmbh, 2))
    else:
        return (get_xmnc_je(p_dwbh, p_ledger, p_kjnd, p_hsxmbh, p_kmbh)
                + get_zwfzys_je(p_dwbh, p_ledger, p_kjnd, p_hsxmbh, p_kmbh, 2)
                - get_zwfzys_je(p_dwbh, p_ledger, p_kjnd, p_hsxmbh, p_kmbh, 1))

def load_xmnm_mx(p_dwbh, p_ledger, p_model, p_qzq_type, p_kjnd, p_xhbh, p_xhmc, p_fybh, p_fymc, p_hsxmbh, p_kmbh, p_flag):
    """
    获取项目科目年末余额明细，保存到收支余明细表中
    注：确保本方法执行前，相关历史或重复记录已被删除
    :param p_dwbh: 单位编号
    :param p_ledger: 账套ID
    :param p_model: 模型
    :param p_qzq_type: 全周期类型
    :param p_kjnd: 会计年度
    :param p_xhbh: 型号编号
    :param p_xhmc: 型号名称
    :param p_fybh: 费用编号
    :param p_fymc: 费用名称
    :param p_hsxmbh: 核算项目编号，允许非末级
    :param p_kmbh: 会计科目编号，允许非末级
    :param p_flag: 金额计算方式, 1/-1
    """
    yefx = get_kmbh_yefx(p_dwbh, p_ledger, p_kjnd, p_kmbh)
    if yefx == 1:
        # 年初部分
        load_xmnc_mx(p_dwbh, p_ledger, p_model, p_qzq_type, p_kjnd, p_xhbh, p_xhmc, p_fybh, p_fymc, p_hsxmbh, p_kmbh, p_flag)
        # 借方发生部分
        load_zwfzys_mx(p_dwbh, p_ledger, p_model, p_qzq_type, p_kjnd, p_xhbh, p_xhmc, p_fybh, p_fymc, p_hsxmbh, p_kmbh, 1, p_flag)
        # 贷方发生部分
        load_zwfzys_mx(p_dwbh, p_ledger, p_model, p_qzq_type, p_kjnd, p_xhbh, p_xhmc, p_fybh, p_fymc, p_hsxmbh, p_kmbh, 2, -1*p_flag)

    else:

        # 年初部分
        load_xmnc_mx(p_dwbh, p_ledger, p_model, p_qzq_type, p_kjnd, p_xhbh, p_xhmc, p_fybh, p_fymc, p_hsxmbh, p_kmbh, p_flag)
        # 贷方发生部分
        load_zwfzys_mx(p_dwbh, p_ledger, p_model, p_qzq_type, p_kjnd, p_xhbh, p_xhmc, p_fybh, p_fymc, p_hsxmbh, p_kmbh, 2, p_flag)
        # 借方发生部分
        load_zwfzys_mx(p_dwbh, p_ledger, p_model, p_qzq_type, p_kjnd, p_xhbh, p_xhmc, p_fybh, p_fymc, p_hsxmbh, p_kmbh, 1, -1*p_flag)

def get_fymx_je(p_dwbh, p_ledger, p_kjnd, p_hsxmbh, p_kmbh, p_calc, p_jsfs) -> float:
    """
    计算一个项目+费用配置明细级别(一个项目编号+科目编号)的结果金额
    :param p_dwbh: 单位编号
    :param p_ledger: 账套ID
    :param p_kjnd: 会计年度
    :param p_hsxmbh: 核算项目编号，允许非末级
    :param p_kmbh: 会计科目编号，允许非末级
    :param p_calc: 计算方式-加减，"+"表示加运算，"-"表示减运算，"减"表示减运算，其他表示加运算
    :param p_jsfs:  计算方式-金额类别，NCYE-项目科目年初余额、JFFS-项目科目借方累计发生金额、DFFS-项目科目贷方方累计发生金额、DQYE-项目科目年末余额、NMYE-项目科目年末余额
    :return: 结果金额
    """
    je = 0
    calc_flag = -1 if (p_calc == '-' or p_calc == '减') else 1
    if p_jsfs == 'NCYE':
        je = calc_flag * get_xmnc_je(p_dwbh, p_ledger, p_kjnd, p_hsxmbh, p_kmbh)
    elif p_jsfs == 'JFFS':
        je = calc_flag * get_zwfzys_je(p_dwbh, p_ledger, p_kjnd, p_hsxmbh, p_kmbh, 1)
    elif p_jsfs == 'DFFS':
        je = calc_flag * get_zwfzys_je(p_dwbh, p_ledger, p_kjnd, p_hsxmbh, p_kmbh, 2)
    elif p_jsfs == 'DQYE':
        je = calc_flag * get_xmnm_je(p_dwbh, p_ledger, p_kjnd, p_hsxmbh, p_kmbh)
    elif p_jsfs == 'NMYE':
        je = calc_flag * get_xmnm_je(p_dwbh, p_ledger, p_kjnd, p_hsxmbh, p_kmbh)
    else:
        je = 0

    return je

def load_fymx_mx(p_dwbh, p_ledger, p_model, p_qzq_type, p_kjnd, p_xhbh, p_xhmc, p_fybh, p_fymc, p_hsxmbh, p_kmbh, p_calc, p_jsfs):
    """
    获取一个项目+费用配置明细级别的来源数据（一个项目编号+科目编号)，保存到收支余明细表中
    注：确保本方法执行前，相关历史或重复记录已被删除
    :param p_dwbh: 单位编号
    :param p_ledger: 账套ID
    :param p_model: 模型
    :param p_qzq_type: 全周期类型
    :param p_kjnd: 会计年度
    :param p_xhbh: 型号编号
    :param p_xhmc: 型号名称
    :param p_fybh: 费用编号
    :param p_fymc: 费用名称
    :param p_hsxmbh: 核算项目编号，允许非末级
    :param p_kmbh: 会计科目编号，允许非末级
    :param p_calc: 计算方式-加减，"+"表示加运算，"-"表示减运算，"减"表示减运算，其他表示加运算
    :param p_jsfs:  计算方式-金额类别，NCYE-项目科目年初余额、JFFS-项目科目借方累计发生金额、DFFS-项目科目贷方方累计发生金额、DQYE-项目科目年末余额、NMYE-项目科目年末余额
    """

    calc_flag = -1 if (p_calc == '-' or p_calc == '减') else 1
    if p_jsfs == 'NCYE':
        load_xmnc_mx(p_dwbh, p_ledger, p_model, p_qzq_type, p_kjnd, p_xhbh, p_xhmc, p_fybh, p_fymc, p_hsxmbh, p_kmbh, calc_flag)
    elif p_jsfs == 'JFFS':
        load_zwfzys_mx(p_dwbh, p_ledger, p_model, p_qzq_type, p_kjnd, p_xhbh, p_xhmc, p_fybh, p_fymc, p_hsxmbh, p_kmbh, 1, calc_flag)
    elif p_jsfs == 'DFFS':
        load_zwfzys_mx(p_dwbh, p_ledger, p_model, p_qzq_type, p_kjnd, p_xhbh, p_xhmc, p_fybh, p_fymc, p_hsxmbh, p_kmbh, 2, calc_flag)
    elif p_jsfs == 'DQYE':
        load_xmnm_mx(p_dwbh, p_ledger, p_model, p_qzq_type, p_kjnd, p_xhbh, p_xhmc, p_fybh, p_fymc, p_hsxmbh, p_kmbh, calc_flag)
    elif p_jsfs == 'NMYE':
        load_xmnm_mx(p_dwbh, p_ledger, p_model, p_qzq_type, p_kjnd, p_xhbh, p_xhmc, p_fybh, p_fymc, p_hsxmbh, p_kmbh, calc_flag)
    else:
        pass


def get_element_je(p_dwbh, p_ledger, p_model, p_qzq_type, p_kjnd, p_code, p_fybh) -> float:
    """
    计算一个型号+费用的结果金额
    :param p_dwbh: 单位编号
    :param p_ledger: 账套ID
    :param p_model: 模块名称
    :param p_qzq_type: 收支余类型
    :param p_kjnd: 会计年度
    :param p_code: 型号编号
    :param p_fybh: 费用编号，例如：K030
    :return: 结果金额
    """
    je = 0
    con = get_connection()
    sql_hsm = f"""
        select distinct kjnd, hsm_xmbh, calc 
          from v_nw_project_hsm hsm
         where hsm.dwbh = $p_dwbh
           and (hsm.kjnd is null or $p_kjnd is null or hsm.kjnd = $p_kjnd) -- 年度
           and hsm.code = $p_code
           and hsm.model = $p_model
           and ( qzq_type is null or $p_qzq_type is null or qzq_type = $p_qzq_type )  -- 收支余类型
    """
    arr_hsm = con.execute(sql_hsm,
                          {'p_dwbh': p_dwbh, 'p_model': p_model, 'p_kjnd': p_kjnd, 'p_code': p_code, 'p_qzq_type':p_qzq_type}).fetchall()


    sql_km = f"""
        select distinct kjnd, kmbh, calc, jsfs 
          from NW_FYMC_MX fy
         where fy.dwbh = $p_dwbh
           and (fy.kjnd is null or $p_kjnd is null or fy.kjnd = $p_kjnd )  -- 年度
           and fy.fybh = $p_fybh
           and fy.model = $p_model
           and fy.status = '1'
        """
    arr_km = con.execute(sql_km, {'p_dwbh':p_dwbh,'p_model':p_model,'p_kjnd':p_kjnd,'p_fybh':p_fybh}).fetchall()
    con.close()

    for hsm in arr_hsm:
        for km in arr_km:
            kjnd_hsm = hsm[0]  # 项目配置表年度可能为空
            kjnd_km = km[0] # 科目配置表年度不为空
            if kjnd_hsm is not None and kjnd_hsm != kjnd_km:
                continue
            else: # 项目配置表年度 或 项目配置表年度 = 科目配置表年度不为空, 两种情况下均以科目配置表年度为准
                l_kjnd = kjnd_km
                l_hsxmbh = hsm[1] # 核算项目编号，允许为非末级
                l_calc_hsm = -1 if (hsm[2] == '1' or hsm[2] == '减') else 1  # 计算方式-加减，"+"表示加运算，"-"表示减运算，"减"表示减运算，其他表示加运算
                l_kmbh = km[1] # 会计科目编号，允许为非末级 # TODO 考虑依据列计算的表达式
                l_calc_km = km[2] # 计算方式-加减，"+"表示加运算，"-"表示减运算，"减"表示减运算，其他表示加运算
                l_jsfs = 'JFFS' if (km[3] is None) else km[3] # 计算方式-金额类别，NCYE-项目科目年初余额、JFFS-项目科目借方累计发生金额、DFFS-项目科目贷方方累计发生金额、DQYE-项目科目年末余额、NMYE-项目科目年末余额
                try:
                    # 计算一个项目编号+科目编号级次的金额
                    # print('计算一个项目编号+科目编号级次的金额',l_kjnd, l_hsxmbh, l_kmbh, l_jsfs)
                    je += get_fymx_je(p_dwbh, p_ledger, l_kjnd, l_hsxmbh, l_kmbh, l_calc_hsm*l_calc_km, l_jsfs)
                except Exception as e:
                    print(e)
                    continue
    return je

def load_element_mx(p_dwbh, p_ledger, p_model, p_qzq_type, p_kjnd, p_code, p_xhmc, p_fybh, p_fymc):
    """
    获取一个型号+费用的结果来源明细数据，保存到收支余明细表中
    注：确保本方法执行前，相关历史或重复记录已被删除
    :param p_dwbh: 单位编号
    :param p_ledger: 账套ID
    :param p_model: 模块名称
    :param p_qzq_type: 收支余类型
    :param p_kjnd: 会计年度
    :param p_code: 型号编号
    :param p_xhmc: 型号名称
    :param p_fybh: 费用编号，例如：K030
    :param p_fymc: 费用名称
    :return: 结果金额
    """
    con = get_connection()
    sql_hsm = f"""
        select distinct kjnd, hsm_xmbh, calc 
          from v_nw_project_hsm hsm
         where hsm.dwbh = $p_dwbh
           and (hsm.kjnd is null or $p_kjnd is null or hsm.kjnd = $p_kjnd) -- 年度
           and hsm.code = $p_code
           and hsm.model = $p_model
           and ( qzq_type is null or $p_qzq_type is null or qzq_type = $p_qzq_type )  -- 收支余类型
    """
    arr_hsm = con.execute(sql_hsm,
                          {'p_dwbh': p_dwbh, 'p_model': p_model, 'p_kjnd': p_kjnd, 'p_code': p_code, 'p_qzq_type':p_qzq_type}).fetchall()


    sql_km = f"""
        select distinct kjnd, kmbh, calc, jsfs 
          from NW_FYMC_MX fy
         where fy.dwbh = $p_dwbh
           and (fy.kjnd is null or $p_kjnd is null or fy.kjnd = $p_kjnd )  -- 年度
           and fy.fybh = $p_fybh
           and fy.model = $p_model
           and fy.status = '1'
        """
    arr_km = con.execute(sql_km, {'p_dwbh':p_dwbh,'p_model':p_model,'p_kjnd':p_kjnd,'p_fybh':p_fybh}).fetchall()
    con.close()

    for hsm in arr_hsm:
        for km in arr_km:
            kjnd_hsm = hsm[0]  # 项目配置表年度可能为空
            kjnd_km = km[0] # 科目配置表年度不为空
            if kjnd_hsm is not None and kjnd_hsm != kjnd_km:
                continue
            else: # 项目配置表年度 或 项目配置表年度 = 科目配置表年度不为空, 两种情况下均以科目配置表年度为准
                l_kjnd = kjnd_km
                l_hsxmbh = hsm[1] # 核算项目编号，允许为非末级
                l_calc_hsm = -1 if (hsm[2] == '1' or hsm[2] == '减') else 1  # 计算方式-加减，"+"表示加运算，"-"表示减运算，"减"表示减运算，其他表示加运算
                l_kmbh = km[1] # 会计科目编号，允许为非末级
                l_calc_km = km[2] # 计算方式-加减，"+"表示加运算，"-"表示减运算，"减"表示减运算，其他表示加运算
                l_jsfs = 'JFFS' if (km[3] is None) else km[3] # 计算方式-金额类别，NCYE-项目科目年初余额、JFFS-项目科目借方累计发生金额、DFFS-项目科目贷方方累计发生金额、DQYE-项目科目年末余额、NMYE-项目科目年末余额
                try:
                    # 加载一个项目编号+科目编号级次的金额
                    # print('加载一个项目编号+科目编号级次的金额',l_kjnd, l_hsxmbh, l_kmbh, l_jsfs)
                    load_fymx_mx(p_dwbh, p_ledger, p_model, p_qzq_type, p_kjnd, p_code, p_xhmc, p_fybh, p_fymc, l_hsxmbh, l_kmbh,
                                 l_calc_hsm*l_calc_km,  l_jsfs)
                except Exception as e:
                    print(e)
                    continue


def calc_szy(p_dwbh, p_ledger, p_model, p_qzq_type=None, p_kjnd=None):
    """
    生成收支余结果表，数据保存到数据库表
    :param p_dwbh: 单位编号
    :param p_ledger: 账套ID
    :param p_model: 模型
    :param p_qzq_type: 收支余类型, 可不传
    :param p_kjnd: 会计年度，可不传
    """
    # 先删除相关收支余结果
    nw_szy_service.delete_data_by_model(p_dwbh, p_model, p_qzq_type)

    con = get_connection()
    sql_xh = f"""
        select distinct code, name 
          from NW_PROJECT
         where dwbh = $p_dwbh
           and model = $p_model
           and ( qzq_type is null or $p_qzq_type is null or qzq_type = $p_qzq_type )  -- 收支余类型
           and status = '1'
    """
    arr_xh = con.execute(sql_xh, {'p_dwbh': p_dwbh, 'p_model': p_model, 'p_qzq_type': p_qzq_type}).fetchall()

    if len(arr_xh) == 0:
        raise Exception('没有找到相关报表行配置, 请先上传再生成报表')

    sql_fy = f"""
        select distinct fybh, fymc 
          from NW_FYMC fy
         where fy.dwbh = $p_dwbh
           and (fy.kjnd is null or $p_kjnd is null or fy.kjnd = $p_kjnd )  -- 年度
           and fy.model = $p_model
           and fy.status = '1'
        """
    arr_fy = con.execute(sql_fy, {'p_dwbh':p_dwbh,'p_model':p_model,'p_kjnd':p_kjnd}).fetchall()
    # print(len(arr_fy))
    if len(arr_fy) == 0:
        raise Exception('没有找到相关报表列配置, 请先上传再生成报表')

    con.close()
    for xh in arr_xh:
        for fy in arr_fy:
            l_xhbh = xh[0]
            l_xhmc = xh[1]
            l_fybh = fy[0]
            l_fymc = fy[1]
            # 计算列金额 通过原始数据表计算
            # print('计算项目：', l_xhbh, l_xhmc, '费用：', l_fybh, l_fymc)
            je = get_element_je(p_dwbh, p_ledger, p_model, p_qzq_type, p_kjnd, l_xhbh, l_fybh)
            # 备选方案 通过收支余结果明细表计算
            # get_element_je_by_szy_mx(p_dwbh, p_ledger, p_model, p_qzq_type, p_kjnd, l_xhbh, l_fybh)
            # 数据入库
            # 1、先删除重复
            # nw_szy_service.delete_data_by_xh_and_fy(p_dwbh, p_model, p_qzq_type, l_xhbh, l_fybh)
            # 2、再保存新数据
            nw_szy_service.add_data(uuid.uuid4(), p_dwbh, p_model, p_qzq_type, l_xhbh, l_xhmc, l_fybh, l_fymc, je)



def calc_szy_mx(p_dwbh, p_ledger, p_model, p_qzq_type=None, p_kjnd=None):
    """
    生成收支余明细结果表，数据保存到数据库表
    :param p_dwbh: 单位编号
    :param p_ledger: 账套ID
    :param p_model: 模型
    :param p_qzq_type: 收支余类型, 可不传
    :param p_kjnd: 会计年度，可不传
    """
    # 先删除相关收支余明细结果
    delete_data_by_model(p_dwbh, p_model, p_qzq_type)

    con = get_connection()
    sql_xh = f"""
        select distinct code, name 
          from NW_PROJECT
         where dwbh = $p_dwbh
           and model = $p_model
           and ( qzq_type is null or $p_qzq_type is null or qzq_type = $p_qzq_type )  -- 收支余类型
           and status = '1'
    """
    arr_xh = con.execute(sql_xh, {'p_dwbh': p_dwbh, 'p_model': p_model, 'p_qzq_type': p_qzq_type}).fetchall()

    if len(arr_xh) == 0:
        raise Exception('没有找到相关报表行配置, 请先上传再生成报表')

    sql_fy = f"""
        select distinct fybh, fymc 
          from NW_FYMC fy
         where fy.dwbh = $p_dwbh
           and (fy.kjnd is null or $p_kjnd is null or fy.kjnd = $p_kjnd )  -- 年度
           and fy.model = $p_model
           and fy.status = '1'
        """
    arr_fy = con.execute(sql_fy, {'p_dwbh':p_dwbh,'p_model':p_model,'p_kjnd':p_kjnd}).fetchall()

    if len(arr_fy) == 0:
        raise Exception('没有找到相关报表列配置, 请先上传再生成报表')

    con.close()
    for xh in arr_xh:
        for fy in arr_fy:
            l_xhbh = xh[0]
            l_xhmc = xh[1]
            l_fybh = fy[0]
            l_fymc = fy[1]

            # 加载列数据
            load_element_mx(p_dwbh, p_ledger, p_model, p_qzq_type, p_kjnd, l_xhbh, l_xhmc, l_fybh, l_fymc)


def get_element_je_by_szy_mx(p_dwbh, p_ledger, p_model, p_qzq_type, p_kjnd, p_code, p_fybh) -> float:
    """
    计算一个型号+费用的结果金额, 通过收支余结果明细表计算
    :param p_dwbh: 单位编号
    :param p_ledger: 账套ID
    :param p_model: 模块名称
    :param p_qzq_type: 收支余类型
    :param p_kjnd: 会计年度
    :param p_code: 型号编号
    :param p_fybh: 费用编号，例如：K030
    :return: 结果金额
    """
    je = 0
    con = get_connection()
    sql_hsm = f"""
        select COALESCE(sum(calc_je), 0)
          from NW_SZY_MX
         where dwbh = $p_dwbh
           and model = $p_model
           and ( qzq_type is null or $p_qzq_type is null or qzq_type = $p_qzq_type )  -- 收支余类型
           and (kjnd is null or $p_kjnd is null or kjnd = $p_kjnd) -- 年度
           and code = $p_code
           and fybh = $p_fybh
    """
    je = con.execute(sql_hsm,
                        {'p_dwbh': p_dwbh, 'p_model': p_model, 'p_kjnd': p_kjnd, 'p_code': p_code, 'p_qzq_type':p_qzq_type,
                        'p_fybh':p_fybh}).fetchone()[0]
    con.close()
    return je




def get_element_je_bak(p_dwbh, p_model, p_ledger, p_kjnd, p_code, p_fybh):
    """
    方法作废
    """
    je = 0 # 最终金额

    con = get_connection()
    sql = f"""
        select count(uuid) from NW_FYMC_MX fy
           where fy.dwbh = $p_dwbh
             and (fy.kjnd is null or $p_kjnd is null or fy.kjnd = $p_kjnd )  -- 年度
             and fy.fybh = $p_fybh
             and fy.model = $p_model
             and fy.status = '1'
             and fy.jsfs in ('JFFS','DFFS')
    """
    jd_count = con.execute(sql, {'p_dwbh':p_dwbh,'p_model':p_model,'p_kjnd':p_kjnd,'p_fybh':p_fybh}).fetchone()[0]

    if jd_count > 0:
        # JFFS 或 DFFS
        sql = f"""
        -- p_dwbh, p_model, p_ledger, p_kjnd, p_code, p_fybh
    select COALESCE(sum(t.je),0) as je from v_temp_all_gscloud t
     where t.dwbh = $p_dwbh
       and t.ledger = $p_ledger
       and (t.year = $p_kjnd or $p_kjnd is null )
       -- 型号关联
       and exists(
          select 1 from v_nw_project_hsm hsm
           where hsm.dwbh = t.dwbh
             and (hsm.kjnd is null or hsm.kjnd = t.year) -- 年度
             and t.zx02bh like hsm.hsm_xmbh||'%'  -- 项目 
             and hsm.code = $p_code
             and hsm.model = $p_model
        )
        -- “减”运算排除的任务号映射
        and not exists(
          select 1 from v_nw_project_hsm hsm
           where hsm.dwbh = t.dwbh
             and (hsm.kjnd is null or hsm.kjnd = t.year) -- 年度
             and hsm.calc = '减' and t.zx02bh like hsm.hsm_xmbh ||'%' -- 项目 注：“减”表示“排除”，逻辑“减某一项目发生额”暂未实现
             and hsm.code = $p_code
             and hsm.model = $p_model
        )
        -- 费用类型关联
       and exists(
          select 1 from NW_FYMC_MX fy
           where fy.dwbh = t.dwbh
             and (fy.kjnd is null or fy.kjnd = t.year) -- 年度
             and t.kmbh like fy.kmbh||'%'  -- 科目
             and (fy.jsfs = 'JFFS' and t.jzfx = 1 or  fy.jsfs = 'DFFS' and t.jzfx = 1)  -- 记账方向 
             and fy.fybh = $p_fybh
             and fy.model = $p_model
             and fy.status = '1'
        )
        -- “减”运算排除的科目映射
        and not exists(
          select 1 from NW_FYMC_MX fy
           where fy.dwbh = t.dwbh
             and (fy.kjnd is null or fy.kjnd = t.year)  -- 年度
             and fy.calc = '减' and t.kmbh like fy.kmbh||'%' -- 科目
             and (fy.jsfs = 'JFFS' and t.jzfx = 1 or  fy.jsfs = 'DFFS' and t.jzfx = 1)  -- 记账方向 注：“减”表示“排除”，逻辑“减某一科目发生额”暂未实现
             and fy.fybh = $p_fybh
             and fy.model = $p_model
             and fy.status = '1'
        )
        """
        je = je + con.execute(sql, {'p_dwbh':p_dwbh,'p_ledger':p_ledger,'p_kjnd':p_kjnd,'p_code':p_code,'p_model':p_model,'p_fybh':p_fybh}).fetchone()[0]
    # 年初部分
    sql = f"""
        select count(uuid) from NW_FYMC_MX fy
           where fy.dwbh = $p_dwbh
             and (fy.kjnd is null or $p_kjnd is null or fy.kjnd = $p_kjnd )  -- 年度
             and fy.fybh = $p_fybh
             and fy.model = $p_model
             and fy.status = '1'
             and fy.jsfs = 'NCYE'
        """
    jd_count = con.execute(sql, {'p_dwbh':p_dwbh,'p_model':p_model,'p_kjnd':p_kjnd,'p_fybh':p_fybh}).fetchone()[0]

    if jd_count > 0:
        sql = f"""
            -- 计算年初余额
        """
        je = je + con.execute(sql, {'p_dwbh':p_dwbh,'p_ledger':p_ledger,'p_kjnd':p_kjnd,'p_code':p_code,'p_model':p_model,'p_fybh':p_fybh}).fetchone()[0]

    # 余额部分
    sql = f"""
        select count(uuid) from NW_FYMC_MX fy
           where fy.dwbh = $p_dwbh
             and (fy.kjnd is null or $p_kjnd is null or fy.kjnd = $p_kjnd )  -- 年度
             and fy.fybh = $p_fybh
             and fy.model = $p_model
             and fy.status = '1'
             and fy.jsfs = 'NMYE'
        """
    jd_count = con.execute(sql, {'p_dwbh':p_dwbh,'p_model':p_model,'p_kjnd':p_kjnd,'p_fybh':p_fybh}).fetchone()[0]

    if jd_count > 0:
        sql = f"""
            -- 计算年末余额
        """
        je = je + con.execute(sql, {'p_dwbh':p_dwbh,'p_ledger':p_ledger,'p_kjnd':p_kjnd,'p_code':p_code,'p_model':p_model,'p_fybh':p_fybh}).fetchone()[0]

    return je

def check_szy_mx_ready(p_dwbh, p_model, p_qzq_type):
    con = get_connection()


    # 获取数据总条数
    total_count = con.execute("""SELECT COUNT(*) 
                 FROM NW_SZY_MX 
                WHERE dwbh = $p_dwbh and model = $p_model 
                  and ($p_qzq_type is null or qzq_type = $p_qzq_type)
                  limit 1
                 """,
                {'p_dwbh':p_dwbh, 'p_model':p_model, 'p_qzq_type':p_qzq_type}).fetchone()[0]
    con.close()

    if total_count == 0:
        return 0
    else:
        return 1

def check_row_define_ready(p_dwbh, p_model, p_qzq_type, p_kjnd=None):
    con = get_connection()

    # 获取数据总条数
    total_count = con.execute("""SELECT COUNT(*) 
                 FROM V_NW_PROJECT_HSM 
                WHERE dwbh = $p_dwbh and model = $p_model and qzq_type = $p_qzq_type
                  and ( kjnd = $p_kjnd or $p_kjnd is null or kjnd is null)
                  limit 1
                 """,
                {'p_dwbh':p_dwbh, 'p_model':p_model, 'p_qzq_type':p_qzq_type, 'p_kjnd':p_kjnd}).fetchone()[0]
    con.close()

    if total_count == 0:
        return 0
    else:
        return 1

def check_column_define_ready(p_dwbh, p_model, p_kjnd=None):
    con = get_connection()


    # 获取数据总条数
    total_count = con.execute("""SELECT COUNT(*) 
                 FROM NW_FYMC_MX 
                WHERE dwbh = $p_dwbh and model = $p_model 
                  and (kjnd = $p_kjnd or $p_kjnd is null)
                  limit 1
                 """,
                {'p_dwbh':p_dwbh, 'p_model':p_model, 'p_kjnd':p_kjnd}).fetchone()[0]

    if total_count == 0:
        con.close()
        return 0
    else:
        # 获取数据总条数
        total_count = con.execute("""SELECT COUNT(*) 
                         FROM NW_FYMC 
                        WHERE dwbh = $p_dwbh and model = $p_model 
                          limit 1
                         """,
                                  {'p_dwbh': p_dwbh, 'p_model': p_model}).fetchone()[0]
        con.close()
        if total_count == 0:
            return 0
        else:
            return 1
def download_excel(p_dwbh, p_model, p_qzq_type):
    con = get_connection()
    # 将查询结果加载到 Pandas DataFrame
    df = con.execute("""SELECT 
                      xhbh as 型号编号, 
                      xhmc as 型号, 
                      fybh as 列编号, 
                      fymc as 列名, 
                      kjnd as 会计年度,
                      kjqj as 会计期间,
                      pzrq as 凭证日期,
                      pzbh as 凭证编号,
                      zdr as 制单人,
                      zy as 摘要,
                      kmbh as 科目编号,
                      kmmc as 科目名称,
                      jzfx as 记账方向,
                      je as 凭证金额,
                      calc_je as 报表计算金额,
                      --bmbh as 部门编号,
                      --bmmc as 部门名称,
                      --wldwbh as 往来单位编号,
                      --wldwmc as 往来单位名称,
                      --zx01bh as 现金流量项目编号,
                      --zx01mc as 现金流量项目名称,
                      --zx01qc as 现金流量项目全称,
                      zx02bh as 主业项目编号,
                      zx02mc as 主业项目名称,
                      zx02qc as 主业项目全称,
                      --zx99bh as 其他项目编号,
                      --zx99mc as 其他项目名称,
                      --zx99qc as 其他项目全称,
                      data_type as 数据来源
                 FROM NW_SZY_MX 
                WHERE dwbh = $p_dwbh and model = $p_model 
                  and ($p_qzq_type is null or qzq_type = $p_qzq_type)
                 order by xhbh, xhbh
                 """,
                {'p_dwbh':p_dwbh, 'p_model':p_model, 'p_qzq_type':p_qzq_type}).fetchdf()

    ## 关闭连接
    con.close()

    # 设置自定义的 Excel 表头
    # 假设查询结果的列是 ['column1', 'column2', 'column3']，你可以自定义列名
    # custom_columns = ['型号', '费用', '金额']  # 自定义表头

    # 如果需要，可以重命名列
    # df.columns = custom_columns  # 将查询结果的列名替换为自定义列名

    # 将 DataFrame 转换为 Excel 文件（保存为 BytesIO 对象）
    output = io.BytesIO()
    with pd.ExcelWriter(output, engine='openpyxl') as writer:
        df.to_excel(writer, index=False, header=True)  # 将透视表写入 Excel

    output.seek(0)


    excel_filename = 'szy_mx.xlsx'  # 生成临时文件路径
    return [excel_filename, output]
